﻿using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;
using System.Data.Sql;

namespace permit.App_Code.DataAccess
{
    public class PrinterAccess
    {
        string strConn = ConfigurationManager.ConnectionStrings["permitConnectionString"].ConnectionString;
        SqlConnection conn;

        public SqlConnection GetConnetcion(string connString)
        {
            conn = new SqlConnection(connString);
            if (conn.State == ConnectionState.Closed)
            {

                conn.Open();
                
            }
            return conn;
        }

        public bool IsPrintName(string prtName)
        {
            SqlCommand command = new SqlCommand();

            command.CommandText = "SELECT pri_Name FROM PrintTable where pri_Name = @pri_Name";
            command.Connection = GetConnetcion(strConn);
            command.Parameters.Add("@pri_Name", SqlDbType.Int, 4, "pri_Name").Value = prtName;

            int count = 0;
            try
            {
                SqlDataReader dr = command.ExecuteReader();
                while (dr.Read())
                {

                    count++;
                }
                dr.Dispose();
                connClose();
                if (count > 0)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
            catch (Exception ex)
            {
                connClose();
                return false;
            }

        }
        public DataSet GetPrintName(int depID)
        {

            SqlCommand command = new SqlCommand();
            command.CommandText = " SELECT pri_Name FROM PrintTable  where dep_ID = @dep_ID GROUP BY pri_Name ";
            command.Connection = GetConnetcion(strConn);
            command.Parameters.Add("@dep_ID", SqlDbType.Int, 4, "dep_ID").Value = depID;
            SqlDataAdapter da = new SqlDataAdapter(command);

            DataSet ds = new DataSet();
            try
            {
                da.Fill(ds);
                da.Dispose();
                connClose();
                return ds;
            }
            catch (Exception ex)
            {
                connClose();
                return null;
            }
        }

        public DataSet GetPrinter(string priName)
        {
            SqlCommand command = new SqlCommand();
            command.CommandText = "SELECT lable_ID, lable_Top, lable_Left, pri_Name, lable_width, lable_Font, lable_FontSize FROM PrintTable WHERE (pri_Name = @pri_Name)";
            command.Connection = GetConnetcion(strConn);
            command.Parameters.Add("@pri_Name", SqlDbType.VarChar, 50, "pri_Name").Value = priName;
            SqlDataAdapter da = new SqlDataAdapter(command);

            DataSet ds = new DataSet();
            try
            {
                da.Fill(ds);
                da.Dispose();
                connClose();
                return ds;
            }
            catch (Exception ex)
            {
                connClose();
                return null;
            }
        }

        public int InsertPrinter(string prtName, int depID)
        {
            DataSet ds = GetPrinter("0");

            int count = 0;
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                SqlCommand command = new SqlCommand();
                command.Connection = GetConnetcion(strConn);
                command.CommandText = "INSERT INTO PrintTable(lable_ID, lable_Top, lable_Left, pri_Name, lable_width, lable_Font, lable_FontSize, dep_ID) VALUES(@lable_ID,@lable_Top,@lable_Left,@pri_Name,@lable_width,@lable_Font,@lable_FontSize, @dep_ID)";
                command.Parameters.Add("@lable_ID", SqlDbType.VarChar, 50, "lable_ID").Value = dr["lable_ID"].ToString();
                command.Parameters.Add("@lable_Top", SqlDbType.Int, 4, "lable_Top").Value = Convert.ToInt32(dr["lable_Top"]);
                command.Parameters.Add("@lable_Left", SqlDbType.Int, 4, "lable_Left").Value = Convert.ToInt32(dr["lable_Left"]);
                command.Parameters.Add("@lable_width", SqlDbType.Int, 4, "lable_width").Value = Convert.ToInt32(dr["lable_width"]);
                command.Parameters.Add("@lable_FontSize", SqlDbType.Int, 4, "lable_FontSize").Value = Convert.ToInt32(dr["lable_FontSize"]);
                command.Parameters.Add("@lable_Font", SqlDbType.VarChar, 50, "lable_Font").Value = dr["lable_Font"].ToString();
                command.Parameters.Add("@pri_Name", SqlDbType.VarChar, 50, "pri_Name").Value = prtName;

                command.Parameters.Add("@dep_ID", SqlDbType.Int, 4, "dep_ID").Value = depID;
                try
                {
                    count = command.ExecuteNonQuery();
                }
                catch (Exception ex)
                {
                    return -1;
                }

            }
            connClose();
            return count;

        }

        /// <summary>
        /// 关闭数据库连接
        /// </summary>
        public void connClose()
        {
            if (conn.State == ConnectionState.Open)
            {//判断数据库的连接状态，如果状态是打开的话就将它关闭

                conn.Close();
            }
        }


        public int PageUp(string priName, int intMove)
        {


            SqlCommand command = new SqlCommand();
            command.Connection = GetConnetcion(strConn);
            command.CommandText = " UPDATE PrintTable SET lable_Top = lable_Top - @lable_Top WHERE (pri_Name = @pri_Name)";

            command.Parameters.Add("@lable_Top", SqlDbType.Int, 4, "lable_Top").Value = intMove;
            command.Parameters.Add("@pri_Name", SqlDbType.VarChar, 50, "pri_Name").Value = priName;
            int count = 0;
            try
            {
                count = command.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                return -1;
            }
            connClose();
            return count;
        }

        public int PageDown(string priName, int intMove)
        {


            SqlCommand command = new SqlCommand();
            command.Connection = GetConnetcion(strConn);
            command.CommandText = " UPDATE PrintTable SET lable_Top = lable_Top + @lable_Top WHERE (pri_Name = @pri_Name)";

            command.Parameters.Add("@lable_Top", SqlDbType.Int, 4, "lable_Top").Value = intMove;
            command.Parameters.Add("@pri_Name", SqlDbType.VarChar, 50, "pri_Name").Value = priName;
            int count = 0;
            try
            {
                count = command.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                return -1;
            }
            connClose();
            return count;
        }

        public int PageLeft(string priName, int intMove)
        {


            SqlCommand command = new SqlCommand();
            command.Connection = GetConnetcion(strConn);
            command.CommandText = " UPDATE PrintTable SET lable_Left = lable_Left - @lable_Left WHERE (pri_Name = @pri_Name)";

            command.Parameters.Add("@lable_Left", SqlDbType.Int, 4, "lable_Left").Value = intMove;
            command.Parameters.Add("@pri_Name", SqlDbType.VarChar, 50, "pri_Name").Value = priName;
            int count = 0;
            try
            {
                count = command.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                return -1;
            }
            connClose();
            return count;
        }


        public int PageRight(string priName, int intMove)
        {


            SqlCommand command = new SqlCommand();
            command.Connection = GetConnetcion(strConn);
            command.CommandText = " UPDATE PrintTable SET lable_Left = lable_Left + @lable_Left WHERE (pri_Name = @pri_Name)";

            command.Parameters.Add("@lable_Left", SqlDbType.Int, 4, "lable_Left").Value = intMove;
            command.Parameters.Add("@pri_Name", SqlDbType.VarChar, 50, "pri_Name").Value = priName;
            int count = 0;
            try
            {
                count = command.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                return -1;
            }
            connClose();
            return count;
        }


        public int LblUp(string lblID, string priName, int intMove)
        {


            SqlCommand command = new SqlCommand();
            command.Connection = GetConnetcion(strConn);
            command.CommandText = "UPDATE PrintTable SET lable_Top = lable_Top - @lable_Top WHERE (pri_Name = @pri_Name) AND (lable_ID = @lable_ID)";

            command.Parameters.Add("@lable_Top", SqlDbType.Int, 4, "lable_Top").Value = intMove;
            command.Parameters.Add("@pri_Name", SqlDbType.VarChar, 50, "pri_Name").Value = priName;
            command.Parameters.Add("@lable_ID", SqlDbType.VarChar, 50, "lable_ID").Value = lblID;
            int count = 0;
            try
            {
                count = command.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                return -1;
            }
            connClose();
            return count;
        }


        public int LblDown(string lblID, string priName, int intMove)
        {


            SqlCommand command = new SqlCommand();
            command.Connection = GetConnetcion(strConn);
            command.CommandText = "UPDATE PrintTable SET lable_Top = lable_Top + @lable_Top WHERE (pri_Name = @pri_Name) AND (lable_ID = @lable_ID)";

            command.Parameters.Add("@lable_Top", SqlDbType.Int, 4, "lable_Top").Value = intMove;
            command.Parameters.Add("@pri_Name", SqlDbType.VarChar, 50, "pri_Name").Value = priName;
            command.Parameters.Add("@lable_ID", SqlDbType.VarChar, 50, "lable_ID").Value = lblID;
            int count = 0;
            try
            {
                count = command.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                return -1;
            }
            connClose();
            return count;
        }


        public int LblLeft(string lblID, string priName, int intMove)
        {


            SqlCommand command = new SqlCommand();
            command.Connection = GetConnetcion(strConn);
            command.CommandText = "UPDATE PrintTable SET lable_Left = lable_Left- @lable_Left WHERE (pri_Name = @pri_Name) AND (lable_ID = @lable_ID)";

            command.Parameters.Add("@lable_Left", SqlDbType.Int, 4, "lable_Left").Value = intMove;
            command.Parameters.Add("@pri_Name", SqlDbType.VarChar, 50, "pri_Name").Value = priName;
            command.Parameters.Add("@lable_ID", SqlDbType.VarChar, 50, "lable_ID").Value = lblID;
            int count = 0;
            try
            {
                count = command.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                return -1;
            }
            connClose();
            return count;
        }


        public int LblRight(string lblID, string priName, int intMove)
        {


            SqlCommand command = new SqlCommand();
            command.Connection = GetConnetcion(strConn);
            command.CommandText = "UPDATE PrintTable SET lable_Left = lable_Left + @lable_Left WHERE (pri_Name = @pri_Name) AND (lable_ID = @lable_ID)";

            command.Parameters.Add("@lable_Left", SqlDbType.Int, 4, "lable_Left").Value = intMove;
            command.Parameters.Add("@pri_Name", SqlDbType.VarChar, 50, "pri_Name").Value = priName;
            command.Parameters.Add("@lable_ID", SqlDbType.VarChar, 50, "lable_ID").Value = lblID;
            int count = 0;
            try
            {
                count = command.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                return -1;
            }
            connClose();
            return count;
        }


        public int SetFontSize(string lblID, string priName, int size)
        {


            SqlCommand command = new SqlCommand();
            command.Connection = GetConnetcion(strConn);
            command.CommandText = "UPDATE PrintTable SET lable_FontSize = @lable_FontSize WHERE (pri_Name = @pri_Name) AND (lable_ID = @lable_ID)";

            command.Parameters.Add("@lable_FontSize", SqlDbType.Int, 4, "lable_FontSize").Value = size;
            command.Parameters.Add("@pri_Name", SqlDbType.VarChar, 50, "pri_Name").Value = priName;
            command.Parameters.Add("@lable_ID", SqlDbType.VarChar, 50, "lable_ID").Value = lblID;
            int count = 0;
            try
            {
                count = command.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                return -1;
            }
            connClose();
            return count;
        }

        public int SetFont(string lblID, string priName, string fontStyle)
        {


            SqlCommand command = new SqlCommand();
            command.Connection = GetConnetcion(strConn);
            command.CommandText = "UPDATE PrintTable SET lable_Font = @lable_Font WHERE (pri_Name = @pri_Name) AND (lable_ID = @lable_ID)";

            command.Parameters.Add("@lable_Font", SqlDbType.VarChar, 50, "lable_Font").Value = fontStyle;
            command.Parameters.Add("@pri_Name", SqlDbType.VarChar, 50, "pri_Name").Value = priName;
            command.Parameters.Add("@lable_ID", SqlDbType.VarChar, 50, "lable_ID").Value = lblID;
            int count = 0;
            try
            {
                count = command.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                return -1;
            }
            connClose();
            return count;
        }

        public DataSet GetPrintLable()
        {

            SqlCommand command = new SqlCommand();
            command.CommandText = " SELECT * FROM PollTable";
            command.Connection = GetConnetcion(strConn);
            SqlDataAdapter da = new SqlDataAdapter(command);

            DataSet ds = new DataSet();
            try
            {
                da.Fill(ds);
                da.Dispose();
                connClose();
                return ds;
            }
            catch (Exception ex)
            {
                connClose();
                return null;
            }
        }

        public void UpdateLable(string left, string top, int id)
        {
            SqlCommand command = new SqlCommand();
            command.Connection = GetConnetcion(strConn);
            command.CommandText = "UPDATE PollTable SET pol_Left = @pol_Left, pol_Top = @pol_Top WHERE (pol_ID = @pol_ID)";

            command.Parameters.Add("@pol_Left", SqlDbType.Int, 4, "pol_Left").Value = Convert.ToInt32(left);
            command.Parameters.Add("@pol_Top", SqlDbType.Int, 4, "pol_Top").Value = Convert.ToInt32(top);
            command.Parameters.Add("@pol_ID", SqlDbType.Int, 4, "pol_ID").Value = id;

            command.ExecuteNonQuery();

            connClose();


        }
        public void UpdateTable(string left, string top, string width, string size, int id)
        {
            SqlCommand command = new SqlCommand();
            command.Connection = GetConnetcion(strConn);
            command.CommandText = "UPDATE PollTable SET pol_Left = @pol_Left, pol_Top = @pol_Top, pol_Width = @pol_Width, pol_Size = @pol_Size WHERE (pol_ID = @pol_ID)";

            command.Parameters.Add("@pol_Left", SqlDbType.Int, 4, "pol_Left").Value = Convert.ToInt32(left);
            command.Parameters.Add("@pol_Top", SqlDbType.Int, 4, "pol_Top").Value = Convert.ToInt32(top);
            command.Parameters.Add("@pol_Width", SqlDbType.Int, 4, "pol_Width").Value = Convert.ToInt32(width);
            command.Parameters.Add("@pol_Size", SqlDbType.Int, 4, "pol_Size").Value = Convert.ToInt32(size);
            command.Parameters.Add("@pol_ID", SqlDbType.Int, 4, "pol_ID").Value = id;

            command.ExecuteNonQuery();

            connClose();


        }
    }
}
